﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Think.Lenovo.Com.Models.Material;
using MySql.Data.MySqlClient;

namespace Think.Lenovo.Com.DataAccess
{
    public class MaterialInfo
    {

        #region 数据操作

        public long AddMaterial(MaterialBulletin Model)
        {
                        #region //信息

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            #region//sql语句
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into ")
                .Append("tbl_MaterialBulletin")
                .Append("(Title, Content, ReleaseTime, MaterialClass, ")
                .Append("MaterialNum)")
                .Append("values")
                .Append("(@Title, @Content, @ReleaseTime, @MaterialClass, ")
                .Append("@MaterialNum)");

            #endregion

            #region//Sql参数数据
            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@Title",MySqlDbType.VarChar,200),
                    new MySqlParameter("@Content",MySqlDbType.Text),
                    new MySqlParameter("@ReleaseTime",MySqlDbType.DateTime),
                    new MySqlParameter("@MaterialClass",MySqlDbType.VarChar,5000),
                    new MySqlParameter("@MaterialNum",MySqlDbType.VarChar,3000)
                };

            ps[0].Value = Model.Title;
            ps[1].Value = Model.Content;
            ps[2].Value = Model.ReleaseTime;
            ps[3].Value = Model.MaterialClass;
            ps[4].Value = Model.MaterialNum;
            #endregion
            long id = MysqlHelper.Insert(conn, sql.ToString(), ps);
            return id;

            #endregion
        }

        public long UpdateMaterial(MaterialBulletin Model)
        {
            #region //信息

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            #region//sql语句
            StringBuilder sql = new StringBuilder();
            sql.Append("Update ")
                .Append("tbl_MaterialBulletin")
                .Append("Set Title=@Title, Content=@Content, ReleaseTime=@Content, MaterialClass=@MaterialClass, ")
                .Append("MaterialNum=@MaterialNum Where Id=@Id");

            #endregion

            #region//Sql参数数据
            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@Title",MySqlDbType.VarChar,200),
                    new MySqlParameter("@Content",MySqlDbType.Text),
                    new MySqlParameter("@ReleaseTime",MySqlDbType.DateTime),
                    new MySqlParameter("@MaterialClass",MySqlDbType.VarChar,5000),
                    new MySqlParameter("@MaterialNum",MySqlDbType.VarChar,3000)
                };

            ps[0].Value = Model.Title;
            ps[1].Value = Model.Content;
            ps[2].Value = Model.ReleaseTime;
            ps[3].Value = Model.MaterialClass;
            ps[4].Value = Model.MaterialNum;
            #endregion
            long id = MysqlHelper.Update(conn, sql.ToString(), ps);
            return id;

            #endregion
        }


        public void DeleteMaterial(int id)
        {
            #region //信息

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            #region//sql语句
            string sql = "delete from tbl_MaterialBulletin Where Id=@Id";

            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@id",MySqlDbType.Int32)
            };
            ps[0].Value = id;
            MysqlHelper.Delete(conn, sql.ToString(), ps);
            #endregion
            #endregion
        }

        public DataSet GetAllMaterial()
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            string sql = "Select * from tbl_materialbulletin order by id desc";
            DataSet ds = new DataSet();
            ds = MySqlHelper.ExecuteDataset(conn, sql.ToString());
            return ds;
        }

        public DataSet GetMaterialById(int id)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            string sql = "Select * from tbl_materialbulletin where id="+id;
            DataSet ds = new DataSet();
            ds = MySqlHelper.ExecuteDataset(conn, sql.ToString());
            return ds;
        }

        //物料公告数量
        public int GetMaterialCount()
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            string sql = "Select count(*) from tbl_materialbulletin";
            return Int32.Parse(MySqlHelper.ExecuteScalar(conn, sql).ToString());
        }
        #endregion

        #region

        /// <summary>
        /// 获取ClassId的包含菜单列表
        /// </summary>
        /// <param name="ClassId"></param>
        /// <returns></returns>
        public DataSet GetClassListByClassId(string ClassId)
        {

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ClassList,ClassTj from tbl_Material_Class");
            strSql.Append(" where ClassID='" + ClassId + "'");
            return MysqlHelper.Query(conn, strSql.ToString());
        }

        /// <summary>
        /// 添加一个菜单项
        /// </summary>
        /// <param name="ClassId"></param>
        /// <param name="ClassName"></param>
        /// <param name="ClassList"></param>
        /// <param name="ClassPre"></param>
        /// <param name="ClassTj"></param>
        /// <returns></returns>
        public long ClassAdd(MaterialModel model)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            StringBuilder strSql = new StringBuilder();

                        strSql.Append("insert into ")
                .Append("tbl_Material_Class")
                .Append("(ClassId, ClassName, ClassList, ClassPre, ")
                .Append("ClassTj, ClassKind)")

                .Append("values")

                .Append("(@ClassId, @ClassName, @ClassList, @ClassPre, ")
                .Append("@ClassTj, @ClassKind)");

                       MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@ClassId",MySqlDbType.VarChar,200),
                    new MySqlParameter("@ClassName",MySqlDbType.VarChar,200),
                    new MySqlParameter("@ClassList",MySqlDbType.VarChar,900),
                    new MySqlParameter("@ClassPre",MySqlDbType.VarChar,200),
                    new MySqlParameter("@ClassTj",MySqlDbType.Int32),
                    new MySqlParameter("@ClassKind",MySqlDbType.Int32)
                };
                       ps[0].Value = model.ClassId;
                       ps[1].Value = model.ClassName;
                       ps[2].Value = model.ClassList;
                       ps[3].Value = model.ClassPre;
                       ps[4].Value = model.ClassTj;
                       ps[5].Value = model.ClassKind;

            return MysqlHelper.Insert(conn, strSql.ToString(),ps);
        }

        /// <summary>
        /// 编辑一个菜单项
        /// </summary>
        /// <param name="ClassId"></param>
        /// <param name="ClassName"></param>
        /// <param name="ClassList"></param>
        /// <param name="ClassPre"></param>
        /// <param name="ClassTj"></param>
        /// <returns></returns>
        public long ClassSave(MaterialModel model)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbl_Material_Class set ");
            strSql.Append("ClassName=@ClassName,");
            strSql.Append("ClassList=@ClassList,");
            strSql.Append("ClassPre=@ClassPre,ClassTj=@ClassTj");
            strSql.Append(" where ClassId=@ClassId ");


            //同步更新子菜单项
            DataSet ds = this.GetSubClassList(model.ClassId);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    string SubClassList = model.ClassList + dr["ClassId"].ToString().Trim() + ",";

                    model.ClassId = dr["ClassId"].ToString().Trim();
                    model.ClassName = dr["ClassName"].ToString().Trim();
                    model.ClassList = SubClassList.ToString().Trim();
                    model.ClassPre = dr["ClassPre"].ToString().Trim();
                    model.ClassTj = model.ClassTj + 1;
                    ClassSave(model);
                }
            }

            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@ClassName",MySqlDbType.VarChar,200),
                    new MySqlParameter("@ClassList",MySqlDbType.VarChar,900),
                    new MySqlParameter("@ClassPre",MySqlDbType.VarChar,200),
                    new MySqlParameter("@ClassTj",MySqlDbType.Int32)
                };
            ps[0].Value = model.ClassName;
            ps[1].Value = model.ClassList;
            ps[2].Value = model.ClassPre;
            ps[3].Value = model.ClassTj;


            return MysqlHelper.Update(conn, strSql.ToString(), ps);
        }

        /// <summary>
        /// 获取该菜单项的所有子菜单项
        /// </summary>
        /// <param name="ClassId"></param>
        /// <returns></returns>
        public DataSet GetSubClassList(string ClassId)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tbl_Material_Class");
            strSql.Append(" where ClassPre='" + ClassId + "' ");
            return MysqlHelper.Query(conn, strSql.ToString());
        }

        /// <summary>
        /// 获取菜单列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetClassList(int ClassKind)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tbl_Material_Class");
            strSql.Append(" where ClassKind=" + ClassKind + "");
            strSql.Append(" Order By ClassList Asc,ClassOrder Asc");
            return MysqlHelper.Query(conn, strSql.ToString());
        }

        public string GetPreClassId(string ClassId)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select ClassPre From tbl_Material_Class");
            strSql.Append(" Where ClassId='" + ClassId + "'  LIMIT 0,1");
            return MysqlHelper.ExecuteScalar(conn, strSql.ToString()).ToString();
        }

        /// <summary>
        /// 删除一个菜单项
        /// </summary>
        /// <param name="ClassId"></param>
        /// <returns></returns>
        public int DelByClassId(string ClassId)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("Delete From tbl_Material_Class");
            strSql.Append(" where ClassId=@ClassId");
            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@ClassId",MySqlDbType.VarChar,200)
                };
            ps[0].Value = ClassId;
            return MysqlHelper.Delete(conn, strSql.ToString(),ps);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Classid"></param>
        /// <returns></returns>
        public string GetWuLiaoName(string Classid)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ClassName From tbl_Material_Class");
            strSql.Append(" where ClassId=@ClassId");
            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@ClassId",MySqlDbType.VarChar,200)
                };
            ps[0].Value = Classid;
            return MysqlHelper.ExecuteScalar(conn, strSql.ToString(), ps).ToString();

        }

        /// <summary>
        /// 更新排序
        /// </summary>
        /// <param name="ClassId"></param>
        /// <param name="ClassOrder"></param>
        /// <returns></returns>
        public bool UpdateClassOrder(string ClassId, int ClassOrder)
        {
            bool flag;
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;


            StringBuilder strSql = new StringBuilder();
            strSql.Append("Update tbl_Material_Class Set ");
            strSql.Append("ClassOrder=@ClassOrder ");
            strSql.Append(" where ClassId=@ClassId");
            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@ClassId",MySqlDbType.Int32)
                };
            ps[0].Value = ClassId;
            ps[1].Value = ClassOrder;
            if (MysqlHelper.Update(conn, strSql.ToString(), ps) > 0)
            {
                flag = true;
            }
            else
            {
                flag = false;
            }

            return flag;
        }
        #endregion
    }
}
